import mysql.connector
from mysql.connector import Error
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime

def build_query():
    query = """
    SELECT
        device_status,
        update_time,
        device_id 
    FROM
        `map_device_his1223`
    WHERE
        device_id = %s
        AND update_time >= %s
        AND update_time < %s
    ORDER BY
        update_time;
    """
    return query

def fetch_device_status_counts(device, start_time, end_time):
    try:
        connection = mysql.connector.connect(
            host="192.168.3.167",
            database="gwza231dev",
            user="tester",
            password="tester1234",
        )
        if connection.is_connected():
            cursor = connection.cursor(dictionary=True)
            query = build_query()
            cursor.execute(query, (device, start_time, end_time))
            result = cursor.fetchall()
            return result

    except Error as e:
        print("Error while connecting to MySQL", e)
        return None

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

start_time = "2024-12-23 10:44:00"
end_time = "2024-12-23 16:44:00"
# device_ids = [
#     "GW15A0","GW15D0","GW1605","GW1686","GW1746","GW1869","GW1D59","GW1D89","GW4214","GW42A8","GW44F1",
#     "GW4526","GW46AF","GW4704","GW488D","GW492A","GW493A","GW4E9D","GW4EA2","GW5014","GW5110","GW522B","GW5297","GW52A8","GW52AD",
#     "GW530D","GW5318","GW533A","GW537A","GW5416","GW5502","GW5616","GW5686","GW568B","GW5691","GW5696","GW56A7","GW56DC","GW579E","GW5809","GW589C","GW58A1",
# ]
device_ids = [
    "GW15A0","GW15D0","GW1605","GW1686","GW1746"
]
all_device_data = pd.DataFrame()
for device in device_ids:
    result = fetch_device_status_counts(device, start_time, end_time)
    print(result)
    if result is not None:
        df = pd.DataFrame(result)
        df['update_time'] = pd.to_datetime(df['update_time'])
        df = df.drop_duplicates(subset=['device_id', 'update_time'])  # 确保每个设备和时间戳的组合是唯一的
        all_device_data = pd.concat([all_device_data, df], ignore_index=True)

# 绘制折线图之前，我们需要为每个设备分别绘制
plt.figure(figsize=(14, 10))
for device in device_ids:
    device_data = all_device_data[all_device_data['device_id'] == device]
    plt.plot(device_data['update_time'], device_data['device_status'], label=device)

plt.xlabel('Update Time')
plt.ylabel('Device Status')
plt.title('Device Status Over Time')
plt.legend(loc='best')
plt.grid(True)
plt.show()